# import file.connection_mysql as a
#
# conn = a.DataBases()
# print(conn.select_sql('select * from students;'))

import pymysql
import configparser


class OperationMysql(object):
    def __init__(self, db='db'):
        config = configparser.ConfigParser()
        config.read(r'G:\软件测试\自动化内容\python-automation-course-17\浩菌\file\config')
        user = config.get(db, 'db_user')
        password = config.get(db, 'db_password')
        host = config.get(db, 'db_host')
        database = config.get(db, 'db_database')
        port = eval(config.get(db, 'db_port'))
        charset = config.get(db, 'db_charset')
        self.db = pymysql.connect(user=user, password=password, host=host, database=database, port=port,
                                  charset=charset)
        print('数据库开始')

    def select_sql(self, sql, params=None):
        try:
            cursor = self.db.cursor()
            return cursor.execute(sql, params)


        except Exception as e:
            print(e)

    def select_sql_fetchall(self, sql, params=None):
        try:
            cursor = self.db.cursor()
            cursor.execute(sql, params)
            ret = cursor.fetchall()
            return ret

        except Exception as e:
            print(e)

    def insert_sql(self, sql, params=None):
        try:
            cursor = self.db.cursor()
            cursor.execute(sql, params)
            self.db.commit()

        except Exception as e:
            print(e)
            self.db.rollback()

    def close_sql(self):
        self.db.close()


class StudentInfo(OperationMysql):
    def register(self):
        print('————————————————————注册————————————————————')
        s_name = input('请输入姓名：')
        s_sex = input('请输入性别：')
        s_hometown = input('请输入地址：')
        s_age = int(input('请输入年龄：'))
        s_class = input('请输入班级：')
        s_card = input('请输入密码：')
        sql = "select * from students where name = %s"
        count = self.select_sql(sql, s_name)
        if count == 1:
            print('您输入的用户名已存在，请从新输入')
        else:
            sql = "insert into students(name,sex,hometown,age,class,card) " \
                  "values (%s, %s, %s, %s, %s, %s);"
            self.insert_sql(sql, [s_name, s_sex, s_hometown, s_age, s_class, s_card])
            print("恭喜您注册成功，以下是您的注册信息，按回车继续")
            sql = "select * from students where name = %s"
            ret = self.select_sql_fetchall(sql, s_name)
            print(ret)
            input('----按回车继续----')

    def login(self):
        print('————————————————————登录————————————————————')
        s_name = input('请输入姓名：')
        s_card = input('请输入密码：')
        sql = "select * from students where name = %s and card = %s;"
        count = self.select_sql(sql, [s_name, s_card])
        if count == 1:
            print('您的信息如下:')
            print(self.select_sql_fetchall(sql, [s_name, s_card]))
            # print(ret)
            input('----按回车继续----')

        else:
            print('您输入的姓名或密码错误')


if __name__ == '__main__':
    while True:
        db = StudentInfo()
        pd = int(input('------------学生信息查询系统------------\n 1注册，2登录，3退出：'))
        if pd == 1:
            db.register()
            continue
        elif pd == 2:
            db.login()
        elif pd == 3:

            pd2 = int(input("您确定退出程序吗？\n 1、退出，2、回到主菜单:"))
            if pd2 == 1:
                print('程序已经退出，感谢您的使用')
                db.close_sql()
                break
            else:
                print('请您继续使用')
                continue
        else:
            print('您输入的数字有误请从新输入')
            continue
